
# encoding:utf-8
import os
import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
import data_exchange as DE
# from typing import Union


# 影响的行范围
def getRows(ws):
    """
    获取xlsx转换为lua配置表得行范围
    :param ws: workSheet
    :return: min_row[int], max_row[int]
    """
    row_index = str(ws.cell(2,1).value or "").split("-")
    min_row = int(row_index[0] or 5)
    max_row = int(row_index[1]) if len(row_index) == 2 and row_index[1] else ws.max_row
    return min_row, max_row


def xlsx2lua_array(export_type):
    """将excel指定列导出为一个或多个数组

    Args:
        export_type (str): 数组元素的所在列[,辅助列]
    """
    export_type = export_type.split(",")
    try:
        val_col = column_index_from_string(export_type[0])
    except:
        raise TypeError("元素列列号必须为英文字母，单元格A3填写错误！")
    
    key_col = None
    if len(export_type) >= 2:
        if export_type[1]:
            try:
                key_col = column_index_from_string(export_type[1])
            except:
                raise TypeError("辅助列列号必须为英文字母，单元格A3填写错误！")

    min_row, max_row = getRows(ws)

    lst = list(ws.columns)[val_col-1][min_row-1:max_row]
    
    d = {}
    for c in lst:
        if key_col:
            data = ws.cell(c.row, key_col).value
            data_type = ws.cell(2, key_col).value
            key = f"{DE.key_data2str(data, data_type)} = "
        else:
            key = ""

        if key in d:
            d[key].append(c.value)
        else:
            d[key] = [c.value]

    res = ""
    for k, v in d.items():
        i = 0
        if res:
            res += ","
        res += f"\n\t{k}{{"
        for c in v:
            if i % 8 == 0:
                res += "\n\t\t"
            res += f'"{c}", '
            i = i + 1
        res += "\n\t}"

    path = f"temp.txt"
    with open(path, "w", encoding = 'utf-8') as f:
        if key_col:
            res = f'{{{res}\n}}'
        f.write(res)
        print(f"{ws.title} 已配置！")
    os.system(path)


def xlsx2lua_table(export_type=0):
    """
    xlsx转换为lua配置表 
    :param min_row: number = 5
    :param max_row: Union[number,None]
    :param type: number = 0 (0:常规Lua表,1:keys&values)
    :return: None
    """
    min_row, max_row = getRows(ws)

    key_cnt = 0
    key_cols = []
    val_cols = []
    for c in ws[1][1:]:
        if c.value:
            if c.value[:1] == 'k':
                key_cols.insert(int(c.value[1:]), c.column)
                key_cnt += 1
            elif c.value[:1] == 'v':
                val_cols.insert(int(c.value[1:]), c.column)

    res = ""
    rngs = ws.iter_rows(min_row = min_row, max_row = max_row)
    for row in rngs:
        # 处理key
        left = ""
        right = ""
        is_diff_from_prev = False
        is_diff_from_next = False
        for key_level, col in enumerate(key_cols):
            cell = row[col-1]
            prev_cell = ws.cell(cell.row-1, col)
            if is_diff_from_prev or cell.value != prev_cell.value:
                is_diff_from_prev = True
                data_type = ws.cell(2, col).value
                left += "\t" * (key_level + 1) + DE.key_data2str(cell.value, data_type) + " = { "
                if key_level < key_cnt - 1:
                    left += "\n"

            next_cell = ws.cell(cell.row+1, col)
            if is_diff_from_next or cell.value != next_cell.value:
                is_diff_from_next = True
                if key_level < key_cnt - 1:
                    right = "\t" * (key_level + 1) + "},\n" + right
                else:
                    right = "},\n" + right

        # 处理value
        keys = ""
        vals = ""
        for _, col in enumerate(val_cols):
            cell = row[col-1]
            if cell.value or cell.value == 0:
                k = ws.cell(3, col).value
                data_type = ws.cell(2,col).value or DE.get_data_type(cell.value)
                # print(f"{cell.value}, {DE.get_data_type(cell.value)}")
                v = DE.data2desc(cell.value, data_type)
                if not export_type:
                    vals += f"{k} = {v}, "
                else:
                    keys += f"{DE.data2desc(k, 's')}, "
                    vals += f"{v}, "

        if not export_type:
            mid = f"{vals}"
        else:
            mid = "\n" + "\t" * (key_cnt + 1) + f"keys = {{ {keys}}},\n" + \
                    "\t" * (key_cnt + 1) + f"values = {{ {vals}}},\n" + \
                    "\t" * key_cnt
        res += left+mid+right

    cfg_name = ws.cell(1, 1).value

    path = f"D:\\workspace\\test\\python\\xlsx2lua\\temp.txt"
    with open(path, "w", encoding = 'utf-8') as f:
        f.write(f'{cfg_name} = {{\n{res}}}')
        print(ws.title + "表已配置！")
    pid = os.system(path)
    # os.system(f'taskkill /IM program.exe /PID {pid} /F')

def get_last_key_col():
    """
    获取表内倒数第2级key所在的列号
    :return: Union[number,None]
    """
    li = [(c.value, c.column) for c in ws[1] if c.value and "k" in c.value]
    if len(li) <= 1:
        return None
    li.sort(key = lambda x:int(x[0][1:]), reverse=True)
    return li[1][1]


# 生成最后1级key（数字id)
def generate_key_id():
    """
    :param last_key_col: 当前最后1级key的列号 Union[str,None]
    :param min_row: number = 5 
    :param max_row: Union[number,None]
    """
    nwb = openpyxl.Workbook()
    nws = nwb.active

    min_row, max_row = getRows(ws)

    if not max_row:
        max_row = ws.max_row

    last_key_col = get_last_key_col()
    if not last_key_col:
        # 如果没有设置任何key列，则以名称列作为最后1级key
        last_key_col = 2    # B列
    
    cnt = 1
    for row in range(min_row, max_row + 1):
        if ws.cell(row, last_key_col).value == ws.cell(row - 1, last_key_col).value:
            cnt += 1
        else:
            cnt = 1
        nws.cell(row - min_row + 1, 1, cnt)
    nwb.save("test.xlsx")
    print("序号表已生成。。。")


if __name__ == "__main__":
    src_path = input('请输入文件路径：')
    if not src_path:
        src_path = "C:\\Users\\chenhao_wu\\Desktop\\黑咒3.xlsx"
    wb = openpyxl.load_workbook(src_path, data_only=True)
    ws = wb.active

    # 生成Lua配置表
    export_type = ws.cell(3, 1).value
    if not export_type or type(export_type) == int:
        xlsx2lua_table(export_type)
    else:
        xlsx2lua_array(export_type)

    # 生成最后1级key（数字id)
    # generate_key_id()